from flask import Flask, request, jsonify , send_file
import base64
import jwt
from datetime import datetime, timezone
import pymysql
import pandas as pd
import io
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
import sql_queries
import config

app = Flask(__name__)

# 你的JWT base64Secret
BASE64_SECRET = 'OTk5ZGNhZmY2YWQyMjllYTMxMzRlZjU0MTI0YTYyZTJlYWJiYWU5OTg2OTRmZTdjZTMwOWY2YWUyYThlMDBmNCAgLQo='

# 将base64Secret解码为字节
key_bytes = base64.b64decode(BASE64_SECRET)

# 模拟数据库
books = []

# # 数据库配置
# config.DB_HOST = '172.27.53.146'
# config.DB_USER = 'develop_beta'
# config.DB_PASSWORD = 'developnc873kdhgaqi^^$%-'
# config.db_land = ('beta_minerva_land')

def decode_jwt(encoded_token):
    try:
        # 解析JWT token
        claims = jwt.decode(encoded_token, key_bytes, algorithms=['HS512'])

        # 提取必要的信息
        app_token = claims.get('app_token')
        dept_array = claims.get('dept')
        user_id = claims.get('sub')
        expired_at = datetime.fromtimestamp(claims['exp'], tz=timezone.utc)

        # 检查过期时间
        if datetime.now(timezone.utc) > expired_at:
            return {'valid': False, 'error': 'Token expired'}

        return {
            'app_token': app_token,
            'dept_array': dept_array,
            'user_id': user_id,
            'expired_at': expired_at,
            'valid': True
        }
    except jwt.ExpiredSignatureError:
        return {'valid': False, 'error': 'Token expired'}
    except jwt.InvalidAlgorithmError:
        return {'valid': False, 'error': 'Invalid algorithm'}
    except Exception as e:
        print(f"Token decoding error: {e}")
        return {'valid': False, 'error': 'Invalid token'}

# excel样式
def apply_excel_styles(workbook):
    worksheet = workbook.active

    # 设置样式
    header_font = Font(bold=True, color="000000")  # 黑色加粗字体
    header_fill = PatternFill(start_color="D3D3D3", end_color="D3D3D3", fill_type="solid")  # 灰色背景
    alignment = Alignment(horizontal="center", vertical="center")  # 居中对齐
    thin_border = Border(
        left=Side(style="thin"),
        right=Side(style="thin"),
        top=Side(style="thin"),
        bottom=Side(style="thin")
    )  # 细边框

    # 应用到标题行
    for cell in worksheet[1]:  # 假设第一行为标题行
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = alignment
        cell.border = thin_border

    # 应用到数据行 (从第二行开始-到最后一行)
    for row in worksheet.iter_rows(min_row=2, max_row=worksheet.max_row):
        for cell in row:
            cell.alignment = alignment
            cell.border = thin_border

    output = io.BytesIO()
    workbook.save(output)
    output.seek(0)
    return output

# 鉴权中间件
@app.before_request
def authenticate():
    encoded_token = request.cookies.get('apptk')
    if not encoded_token:
        return jsonify({'error': 'Unauthorized'}), 401

    token_data = decode_jwt(encoded_token)
    if not token_data['valid']:
        return jsonify({'error': token_data['error']}), 401

# 通用接口
@app.route('/export/general/<string:type>', methods=['GET'])
def export_general(type):
    try:
        if type not in sql_queries.SQL_GENERAL:
            return jsonify({'error': 'Invalid query type'}), 400
        query = sql_queries.SQL_GENERAL[type]

        connection = pymysql.connect(
            host=config.DB_HOST,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            db=config.db_land
        )

        try:
            df = pd.read_sql(query, connection)
        finally:
            connection.close()

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name='Unrelated Products')

        output.seek(0)
        workbook = load_workbook(output)
        final_output = apply_excel_styles(workbook)

        return send_file(
            final_output,
            download_name=f'{type}.xlsx',
            as_attachment=True,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
    except Exception as e:
        print(f"Error executing SQL: {e}")
        return jsonify({'error': 'Error executing SQL'}), 400

# 查询国际物流-未发货详情数据
@app.route('/export/international_logistics_not_shipped', methods=['GET'])
def international_logistics_not_shipped():
    start_date = request.args.get('startDate')
    if not start_date or start_date is None:
        return jsonify({'error': 'Time Error'}), 400

    query = '''
        select s.name                                                                        as '发货账号',
           case d.project when 1 then '墙纸' when 2 then '地板贴' when 3 then '挂毯' end as '项目',
           mlc.name                                                                      as '物流渠道',
           d.product_name                                                                as '产品名称',
           d.coding                                                                      as '备货编号',
           mso.remark                                                                    as '备货备注',
           d.fba_transfer_orders                                                         as 'FBA调拨单号',
           mlc2.warehouse_id                                                             as '海外仓',
           d.created_at                                                                  as '国内发货时间',
           d.shipping_box                                                                as '发货箱数',
           d.shipped_box                                                                 as '已发货箱数'
        from minerva_logistics_ship_international_delivery d
             left join minerva_sale_account s
                       on s.id = d.sale_account_id
             left join minerva_logistics_channel mlc
                       on mlc.id = d.logistics_channel_id
             left join minerva_logistics_costs mlc2
                       on d.overseas_warehouse_id = mlc2.id
             left join minerva_stocking_order mso
                       on mso.coding = d.coding and mso.is_delete =0
        where d.is_delete = 0
          and d.status != 3
          and d.created_at > %s
        order by d.created_at desc
    '''
    try:
        connection = pymysql.connect(
            host=config.DB_HOST,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            db=config.db_land
        )

        try:
            df = pd.read_sql(query, connection,params=[start_date])
        finally:
            connection.close()

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name='International Logistics Not Shipped')

        output.seek(0)
        workbook = load_workbook(output)
        final_output = apply_excel_styles(workbook)

        return send_file(
            final_output,
            download_name=f'international_logistics_not_shipped.xlsx',
            as_attachment=True,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
    except Exception as e:
        print(f"Error executing SQL: {e}")
        return jsonify({'error': 'Error executing SQL'}), 400

# 销量-日出单-父体利润
@app.route('/export/day_sales_parent', methods=['GET'])
def day_sales_parent():
    start_date = request.args.get('startDate')
    end_date = request.args.get('endDate')
    if not start_date or not end_date or start_date is None or end_date is None:
        return jsonify({'error': 'Time Error'}), 400
    if start_date > end_date:
        return jsonify({'error': 'Time Error'}), 400
    query = '''
        select 
            v.shop_master_name as `店铺&账号`, 
            CASE
                WHEN v.project = 1 THEN '墙纸'
                WHEN v.project = 2 THEN '地板贴'
                WHEN v.project = 3 THEN '挂毯'
                WHEN v.project = 4 THEN '其他'
                WHEN v.project = 5 THEN '忙书'
                ELSE '未知项目'
            END AS `项目`,
            v.product_name as `产品名称`, 
            CASE 
                WHEN v.product_level_key = 'level_five' THEN '四级产品'
                WHEN v.product_level_key = 'level_four' THEN '小三级产品'
                WHEN v.product_level_key = 'level_one' THEN '一级产品'
                WHEN v.product_level_key = 'level_three' THEN '三级产品'
                WHEN v.product_level_key = 'level_two' THEN '二级产品'
                ELSE '未知产品等级'
            END AS `产品等级`,
            v.product_quantity as `销量`,
            v.product_quantity_refund as `退货销量`,
            v.amazon_parent_asin as `父ASIN`,
            v.asin as `子ASIN`,
            v.salesperson_name as `运营`,
            v.product_quantity_days as `日出单`,
            v1.sys_gross_profit as `父体利润`
        from (
            select
                r.shop_master_id,
                min(r.project) as `project`,
                min(masm.shop_name) as `shop_master_name`,
                min(mssm.product_name) as `product_name`,
                min(mssm.product_level_key) as `product_level_key`,
                sum(r.product_quantity) as `product_quantity`,
                sum(r.product_quantity_refund) as `product_quantity_refund`,
                r.amazon_parent_asin,
                r.asin,
                min(me.name) as `salesperson_name`,
                sum(r.product_quantity) / 31 as `product_quantity_days`
            from minerva_amazon_order_sku_report r
            left join {{config.db_auth}}.minerva_employee me on r.salesperson_id = me.id
            left join minerva_amazon_shop_master masm on r.shop_master_id = masm.id
            left join minerva_smart_stocking_master mssm on mssm.amazon_shop_id = r.shop_master_id and mssm.asin = r.asin
            where r.sum_date between %s and %s
            group by r.shop_master_id, r.amazon_parent_asin, r.asin
        ) v
        left join
        (
            select
                e.shop_master_id,
                e.amazon_parent_asin,
                sum(e.sys_gross_profit_cny) / 100 as sys_gross_profit
            from minerva_amazon_finance_event_sum e
            where e.local_date between %s and %s
            and e.calculation_dimension in (2,3,5,7)
            group by e.shop_master_id, e.amazon_parent_asin
        ) v1 on v.shop_master_id = v1.shop_master_id
        and v.amazon_parent_asin = v1.amazon_parent_asin
    '''
    try:
        connection = pymysql.connect(
            host=config.DB_HOST,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            db=config.db_land
        )

        try:
            df = pd.read_sql(query, connection,params=[start_date, end_date, start_date, end_date])
        finally:
            connection.close()

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name='International Logistics Not Shipped')

        output.seek(0)
        workbook = load_workbook(output)
        final_output = apply_excel_styles(workbook)

        return send_file(
            final_output,
            download_name=f'销量-日出单-父体利润_{start_date}~{end_date}.xlsx',
            as_attachment=True,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
    except Exception as e:
        print(f"Error executing SQL: {e}")
        return jsonify({'error': 'Error executing SQL'}), 400

# 按时间区间查询产品即时销售-子ASIN维度(美元)
@app.route('/export/sku_report_asin_usd', methods=['GET'])
def sku_report_asin_usd():
    start_date = request.args.get('startDate')
    end_date = request.args.get('endDate')

    sku_report_asin_usd = "子ASIN维度(美元)"
    filename = f'{sku_report_asin_usd}_{start_date}~{end_date}.xlsx'

    if not start_date or not end_date or start_date is None or end_date is None:
        return jsonify({'error': 'Time Error'}), 400
    if start_date > end_date:
        return jsonify({'error': 'Time Error'}), 400

    query = '''
        select min(v3.account_name)                  as '账号',
           min(v3.shop_name)                     as '店铺',
           min(v2.amazon_product_name)           as '产品信息',
           min(v1.amazon_parent_asin)            as '父ASIN',
           v1.asin,
           min(v1.sku)                           as 'sku',
           min(me.name)                          as '关联运营',
           sum(v1.order_quantity)                as '订单量',
           sum(v1.product_quantity)              as '销量',
           sum(v1.total_amount_usd) / 100        as '销售额',
           sum(v1.order_quantity_refund)         as '退货单量',
           sum(v1.product_quantity_refund)       as '退货销量',
           sum(v1.total_amount_refund_usd) / 100 as '退货销售额'
        from minerva_amazon_order_sku_report v1
                 left join minerva_smart_stocking_master v2
                           on v1.asin = v2.asin
                               and v1.shop_master_id = v2.amazon_shop_id
                 left join {{config.db_auth}}.minerva_employee me
                           on v2.salesperson_id = me.id
                 left join minerva_amazon_shop_master v3
                           on v1.shop_master_id = v3.id
        where v1.sum_date between %s and %s
        group by v1.shop_master_id, v1.asin
    '''

    try:
        connection = pymysql.connect(
            host=config.DB_HOST,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            db=config.db_land
        )

        try:
            df = pd.read_sql(query, connection,params=[start_date, end_date])
        finally:
            connection.close()

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name = sku_report_asin_usd)

        output.seek(0)
        workbook = load_workbook(output)
        final_output = apply_excel_styles(workbook)

        return send_file(
            final_output,
            download_name=filename,
            as_attachment=True,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )

    except Exception as e:
        print(f"Error executing SQL: {e}")
        return jsonify({'error': 'Error executing SQL'}), 400

# 胡羽-删除订单导出-最后更新时间
@app.route('/export/last_updated_at', methods=['GET'])
def last_updated_at():
    start_date = request.args.get('startDate')
    end_date = request.args.get('endDate')

    sheet_name = "最后更新时间"
    file_name = f'{sheet_name}_{start_date}~{end_date}.xlsx'

    if not start_date or not end_date or start_date is None or end_date is None:
        return jsonify({'error': 'Time Error'}), 400
    if start_date > end_date:
        return jsonify({'error': 'Time Error'}), 400
    query = '''
        select t.*,
           o.last_updated_date   as '最后一次的更新时间',
           o.requested_quantity  as '请求移除的商品数量',
           o.cancelled_quantity  as '取消移除的数量',
           o.disposed_quantity   as '已弃置的数量',
           o.shipped_quantity    as '已退回或弃置的数量',
           o.in_process_quantity as '处理中的数量',
           o.request_date        as '提交删除订单的时间',
           o.order_type          as '订单类型'
        from minerva_amazon_fba_removal_order o
                 join (select ac.id,
                              ac.account_name,
                              group_concat(distinct e.name)         as '运营',
                              group_concat(distinct m.asin)         as 'asin',
                              group_concat(distinct m.product_name) as '产品名称',
                              s.seller_sku,
                              s.fn_sku
                       from minerva_smart_stocking_inventory_sale s
                                join minerva_smart_stocking_master m on s.amazon_shop_id = m.amazon_shop_id and s.asin = m.asin
                                join minerva_amazon_shop_account ac on m.account_id = ac.id
                                left join {{config.db_auth}}.minerva_employee e on m.salesperson_id = e.id
                       where s.alive_flag in (0, 1)
                       group by ac.id, ac.account_name, s.seller_sku, s.fn_sku) t
                      on o.account_id = t.id and o.sku = t.seller_sku and o.fnsku = t.fn_sku
        where last_updated_date between %s and %s
    '''

    try:
        connection = pymysql.connect(
            host=config.DB_HOST,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            db=config.db_land
        )

        try:
            df = pd.read_sql(query, connection,params=[start_date, end_date])
        finally:
            connection.close()

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name=sheet_name)

        output.seek(0)
        workbook = load_workbook(output)
        final_output = apply_excel_styles(workbook)

        return send_file(
            final_output,
            download_name=file_name,
            as_attachment=True,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )

    except Exception as e:
        print(f"Error executing SQL: {e}")
        return jsonify({'error': 'Error executing SQL'}), 400

# 财务组事件数据
@app.route('/export/finance_group_events', methods=['GET'])
def finance_group_events():
    start_date = request.args.get('startDate')
    end_date = request.args.get('endDate')

    sheet_name = "财务组事件数据"
    file_name = f'{sheet_name}_{start_date}~{end_date}.xlsx'

    if not start_date or not end_date or start_date is None or end_date is None:
        return jsonify({'error': 'Time Error'}), 400
    if start_date > end_date:
        return jsonify({'error': 'Time Error'}), 400
    query = '''
        select t.*,
           o.last_updated_date   as '最后一次的更新时间',
           o.requested_quantity  as '请求移除的商品数量',
           o.cancelled_quantity  as '取消移除的数量',
           o.disposed_quantity   as '已弃置的数量',
           o.shipped_quantity    as '已退回或弃置的数量',
           o.in_process_quantity as '处理中的数量',
           o.request_date        as '提交删除订单的时间',
           o.order_type          as '订单类型'
        from minerva_amazon_fba_removal_order o
                 join (select ac.id,
                              ac.account_name,
                              group_concat(distinct e.name)         as '运营',
                              group_concat(distinct m.asin)         as 'asin',
                              group_concat(distinct m.product_name) as '产品名称',
                              s.seller_sku,
                              s.fn_sku
                       from minerva_smart_stocking_inventory_sale s
                                join minerva_smart_stocking_master m on s.amazon_shop_id = m.amazon_shop_id and s.asin = m.asin
                                join minerva_amazon_shop_account ac on m.account_id = ac.id
                                left join {{config.db_auth}}.minerva_employee e on m.salesperson_id = e.id
                       where s.alive_flag in (0, 1)
                       group by ac.id, ac.account_name, s.seller_sku, s.fn_sku) t
                      on o.account_id = t.id and o.sku = t.seller_sku and o.fnsku = t.fn_sku
        where last_updated_date between %s and %s
    '''

    try:
        connection = pymysql.connect(
            host=config.DB_HOST,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            db=config.db_land
        )

        try:
            df = pd.read_sql(query, connection,params=[start_date, end_date])
        finally:
            connection.close()

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name=sheet_name)

        output.seek(0)
        workbook = load_workbook(output)
        final_output = apply_excel_styles(workbook)

        return send_file(
            final_output,
            download_name=file_name,
            as_attachment=True,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )

    except Exception as e:
        print(f"Error executing SQL: {e}")
        return jsonify({'error': 'Error executing SQL'}), 400

# 欧洲每日sku-report销量
@app.route('/export/europe_daily_sku_report', methods=['GET'])
def europe_daily_sku_report():
    start_date = request.args.get('startDate')
    end_date = request.args.get('endDate')

    sheet_name = "欧洲每日销售数据"
    file_name = f'{sheet_name}_{start_date}~{end_date}.xlsx'

    if not start_date or not end_date or start_date is None or end_date is None:
        return jsonify({'error': 'Time Error'}), 400
    if start_date > end_date:
        return jsonify({'error': 'Time Error'}), 400
    query = '''
        select sum_date                                          as '日期',
               s.shop_name                                       as `店铺`,
               sum(r.order_quantity)                             as `订单量`,
               sum(r.product_quantity)                           as `销量`,
               sum(r.order_quantity_refund)                      as `退货单量`,
               sum(r.product_quantity_refund)                    as `退货销量`,
               truncate(sum(r.total_amount_usd) / 100, 2)        as `销售额(美元)`,
               truncate(sum(r.total_amount_refund_usd) / 100, 2) as `退货销售额(美元)`
        from minerva_amazon_order_sku_report r
                 join minerva_amazon_shop_master s on s.id = r.shop_master_id
        where r.sum_date between %s and %s
          and r.shop_master_id != ''
          and r.currency in ('eur','gbp','sek','pln','try')
        group by r.shop_master_id, sum_date
        order by r.shop_master_id;
    '''

    try:
        connection = pymysql.connect(
            host=config.DB_HOST,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            db=config.db_land
        )

        try:
            df = pd.read_sql(query, connection,params=[start_date, end_date])
        finally:
            connection.close()

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name=sheet_name)

        output.seek(0)
        workbook = load_workbook(output)
        final_output = apply_excel_styles(workbook)

        return send_file(
            final_output,
            download_name=file_name,
            as_attachment=True,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )

    except Exception as e:
        print(f"Error executing SQL: {e}")
        return jsonify({'error': 'Error executing SQL'}), 400

# 欧洲每月sku-report销量
@app.route('/export/europe_monthly_sku_report', methods=['GET'])
def europe_monthly_sku_report():
    start_date = request.args.get('startDate')
    end_date = request.args.get('endDate')

    sheet_name = "欧洲每月销售数据"
    file_name = f'{sheet_name}_{start_date}~{end_date}.xlsx'

    if not start_date or not end_date or start_date is None or end_date is None:
        return jsonify({'error': 'Time Error'}), 400
    if start_date > end_date:
        return jsonify({'error': 'Time Error'}), 400
    query = '''
        select s.shop_name                                       as `店铺`,
               year(sum_date)                                    as '年份',
               month(sum_date)                                   as '月份',
               sum(r.order_quantity)                             as `订单量`,
               sum(r.product_quantity)                           as `销量`,
               sum(r.order_quantity_refund)                      as `退货单量`,
               sum(r.product_quantity_refund)                    as `退货销量`,
               truncate(sum(r.total_amount_usd) / 100, 2)        as `销售额(美元)`,
               truncate(sum(r.total_amount_refund_usd) / 100, 2) as `退货销售额(美元)`
        from minerva_amazon_order_sku_report r
                 join minerva_amazon_shop_master s on s.id = r.shop_master_id
        where r.sum_date between %s and %s
          and r.shop_master_id != ''
          and r.currency in ('eur','gbp','sek','pln','try')
        group by r.shop_master_id, year(sum_date), month(sum_date)
        order by `店铺`;
    '''

    try:
        connection = pymysql.connect(
            host=config.DB_HOST,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            db=config.db_land
        )

        try:
            df = pd.read_sql(query, connection,params=[start_date, end_date])
        finally:
            connection.close()

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name=sheet_name)

        output.seek(0)
        workbook = load_workbook(output)
        final_output = apply_excel_styles(workbook)

        return send_file(
            final_output,
            download_name=file_name,
            as_attachment=True,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )

    except Exception as e:
        print(f"Error executing SQL: {e}")
        return jsonify({'error': 'Error executing SQL'}), 400

# 即时利润每日原始cny导出
@app.route('/export/immediateProfit_daily_original_cny', methods=['GET'])
def immediateProfit_daily_original_cny():
    start_date = request.args.get('startDate')
    end_date = request.args.get('endDate')

    sheet_name = "即时利润每日原始cny"
    file_name = f'{sheet_name}_{start_date}~{end_date}.xlsx'

    if not start_date or not end_date or start_date is None or end_date is None:
        return jsonify({'error': 'Time Error'}), 400
    if start_date > end_date:
        return jsonify({'error': 'Time Error'}), 400
    query = '''
        select r.local_date                                                        as 日期,
               min(masm.shop_name)                                                 as '店铺',
               r.amazon_parent_asin                                                as '父ASIN',
               r.asin                                                              as '子ASIN',
               case min(mssm.is_new) when 0 then '老品' when 1 then '新品' end      as '新老品',
        #        max(truncate(r.product_original_price, 0))                          as '产品原价(系统产品价格)',
        #        max(truncate(r.product_cost_price, 0))                              as '产品成本价(月度产品成本)',
               if(min(r.profit_normal_at) = '9999-01-01 08:00:00.000000', '',
                  min(r.profit_normal_at))                                         as '利润回正时间',
               if(min(r.profit_turnaround_at) = '9999-01-01 08:00:00.000000', '',
                  min(r.profit_turnaround_at))                                     as '利润扳正时间',
               if(min(r.fba_inventory_at) = '9999-01-01 08:00:00.000000', '',
                  min(r.fba_inventory_at))                                         as '上架时间',
               e.name                                                              as '运营人员',
               max(r.product_name)                                                 as '产品名称',
               ifnull(sum(r.total_quantity), 0)                                    as '总订单量',
               ifnull(sum(r.total_sales_quantity), 0)                              as '总销量',
               ifnull(sum(order_sales_quantity) / sum(order_quantity), 0)          as '一单多买',
               ifnull(sum(r.order_quantity), 0)                                    as '标准订单量',
               ifnull(sum(r.order_sales_quantity), 0)                              as '标准销量',
               ifnull(sum(r.liquidations_quantity), 0)                             as '清仓订单量',
               ifnull(sum(r.liquidations_sales_quantity), 0)                       as '清仓销量',
               ifnull(sum(r.refund_quantity), 0)                                   as '退货订单量',
               ifnull(sum(r.refund_sales_quantity), 0)                             as '退货销量',
               ifnull(sum(r.income_amount_cny), 0) / 100                           as '销售额',
               ifnull(sum(r.refund_amount_cny), 0) / 100                           as '退款额',
               ifnull(sum(r.remote_delivery_amount_cny), 0) / 100                  as '远程配送费',
               ifnull(sum(r.liquidation_amount_cny), 0) / 100                      as '批量清仓',
               ifnull(sum(r.commission_amount_cny), 0) / 100                       as '佣金',
               ifnull(sum(r.delivery_amount_cny), 0) / 100                         as '配送费',
               ifnull(sum(r.promotion_amount_cny), 0) / 100                        as '促销费',
               ifnull(sum(r.remote_delivery_chargeback_amount_cny), 0) / 100       as '远程配送费(抵消项)',
               ifnull(sum(r.coupon_redemption_amount_cny), 0) / 100                as 'coupon优惠券',
               ifnull(sum(r.sp_advertised_amount_cny), 0) / 100                    as '商品推广费',
               ifnull(sum(r.sd_advertised_amount_cny), 0) / 100                    as '商品展示费',
               ifnull(sum(r.sb_advertised_amount_cny), 0) / 100                    as '品牌广告费',
               ifnull(sum(r.lightning_deal_amount_cny), 0) / 100                   as '秒杀',
               ifnull(sum(r.storage_monthly_amount_cny), 0) / 100                  as 'FBA月度仓储费',
               ifnull(sum(r.storage_longterm_amount_cny), 0) / 100                 as 'FBA长期仓储费',
               ifnull(sum(r.storage_disposal_amount_cny), 0) / 100                 as 'FBA移除费',
               ifnull(sum(r.storage_moving_amount_cny), 0) / 100                   as 'FBA移仓费',
               ifnull(sum(r.other_amount_cny), 0) / 100                            as '其他',
               ifnull(sum(r.amazon_total_charge_cny), 0) / 100                     as '亚马逊总扣费',
               ifnull(sum(r.amazon_total_profit_cny), 0) / 100                     as '亚马逊总利润',
               ifnull(sum(r.tax_amount_cny), 0) / 100                              as '税金',
               ifnull(sum(truncate(r.sys_operating_expenses_cny, 0)), 0) / 100     as '运营费',
               ifnull(sum(truncate(r.sys_estimated_product_cost_cny, 0)), 0) /
               100                                                                 as '预估产品成本',
               ifnull(sum(r.sys_estimated_logistics_cny), 0) / 100                 as '预估头程',
               ifnull(sum(truncate(r.sys_estimated_gross_profit_cny, 0)), 0) / 100 as '预估毛利润'
        from minerva_amazon_finance_event_sum r
                 join {{config.db_auth}}.minerva_employee e
                      on r.salesperson_id = e.id
                 left join minerva_smart_stocking_master mssm
                           on r.shop_master_id = mssm.amazon_shop_id
                               and r.asin = mssm.asin
                               and alive_flag in (0, 1)
                 left join minerva_amazon_shop_master masm
                           on masm.id = r.shop_master_id
        where calculation_dimension in (2, 3, 5, 7)
          and local_date between %s and %s
        group by r.salesperson_id, r.amazon_parent_asin, r.asin, r.shop_master_id, r.local_date
        order by 店铺;
    '''

    try:
        connection = pymysql.connect(
            host=config.DB_HOST,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            db=config.db_land
        )

        try:
            df = pd.read_sql(query, connection,params=[start_date, end_date])
        finally:
            connection.close()

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name=sheet_name)

        output.seek(0)
        workbook = load_workbook(output)
        final_output = apply_excel_styles(workbook)

        return send_file(
            final_output,
            download_name=file_name,
            as_attachment=True,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )

    except Exception as e:
        print(f"Error executing SQL: {e}")
        return jsonify({'error': 'Error executing SQL'}), 400


# 财务月度即时子asin原始月份cny
@app.route('/export/immediateProfit_monthly_original_cny', methods=['GET'])
def immediateProfit_monthly_original_cny():
    start_date = request.args.get('startDate')
    end_date = request.args.get('endDate')

    sheet_name = "即时利润每月原始cny"
    file_name = f'{sheet_name}_{start_date}~{end_date}.xlsx'

    if not start_date or not end_date or start_date is None or end_date is None:
        return jsonify({'error': 'Time Error'}), 400
    if start_date > end_date:
        return jsonify({'error': 'Time Error'}), 400
    query = '''
        select min(masm.shop_name)                                                 as '店铺',
               r.amazon_parent_asin                                                as '父ASIN',
               r.asin                                                              as '子ASIN',
               case min(mssm.is_new) when 0 then '老品' when 1 then '新品' end     as '新老品',
        #        max(truncate(r.product_original_price, 0))                          as '产品原价(系统产品价格)',
        #        max(truncate(r.product_cost_price, 0))                              as '产品成本价(月度产品成本)',
               if(min(r.profit_normal_at) = '9999-01-01 08:00:00.000000', '',
                  min(r.profit_normal_at))                                         as '利润回正时间',
               if(min(r.profit_turnaround_at) = '9999-01-01 08:00:00.000000', '',
                  min(r.profit_turnaround_at))                                     as '利润扳正时间',
               if(min(r.fba_inventory_at) = '9999-01-01 08:00:00.000000', '',
                  min(r.fba_inventory_at))                                         as '上架时间',
               e.name                                                              as '运营人员',
               max(r.product_name)                                                 as '产品名称',
               ifnull(sum(r.total_quantity), 0)                                    as '总订单量',
               ifnull(sum(r.total_sales_quantity), 0)                              as '总销量',
               ifnull(sum(order_sales_quantity) / sum(order_quantity), 0)          as '一单多买',
               ifnull(sum(r.order_quantity), 0)                                    as '标准订单量',
               ifnull(sum(r.order_sales_quantity), 0)                              as '标准销量',
               ifnull(sum(r.liquidations_quantity), 0)                             as '清仓订单量',
               ifnull(sum(r.liquidations_sales_quantity), 0)                       as '清仓销量',
               ifnull(sum(r.refund_quantity), 0)                                   as '退货订单量',
               ifnull(sum(r.refund_sales_quantity), 0)                             as '退货销量',
               ifnull(sum(r.income_amount_cny), 0) / 100                           as '销售额',
               ifnull(sum(r.refund_amount_cny), 0) / 100                           as '退款额',
               ifnull(sum(r.remote_delivery_amount_cny), 0) / 100                  as '远程配送费',
               ifnull(sum(r.liquidation_amount_cny), 0) / 100                      as '批量清仓',
               ifnull(sum(r.commission_amount_cny), 0) / 100                       as '佣金',
               ifnull(sum(r.delivery_amount_cny), 0) / 100                         as '配送费',
               ifnull(sum(r.promotion_amount_cny), 0) / 100                        as '促销费用',
               ifnull(sum(r.remote_delivery_chargeback_amount_cny), 0) / 100       as '远程配送费(抵消项)',
               ifnull(sum(r.coupon_redemption_amount_cny), 0) / 100                as 'coupon优惠券',
               ifnull(sum(r.sp_advertised_amount_cny), 0) / 100                    as '商品推广费',
               ifnull(sum(r.sd_advertised_amount_cny), 0) / 100                    as '商品展示费',
               ifnull(sum(r.sb_advertised_amount_cny), 0) / 100                    as '品牌广告费',
               -- ifnull(sum(r.advertising_amount_cny), 0) / 100                      as '广告费用',
               ifnull(sum(r.lightning_deal_amount_cny), 0) / 100                   as '秒杀',
               ifnull(sum(r.storage_monthly_amount_cny), 0) / 100                  as 'FBA月度仓储费',
               ifnull(sum(r.storage_longterm_amount_cny), 0) / 100                 as 'FBA长期仓储费',
               ifnull(sum(r.storage_disposal_amount_cny), 0) / 100                 as 'FBA移除费',
               ifnull(sum(r.storage_moving_amount_cny), 0) / 100                   as '移仓费',
               ifnull(sum(r.other_amount_cny), 0) / 100                            as '其他',
               ifnull(sum(r.amazon_total_charge_cny), 0) / 100                     as '亚马逊总扣费',
               ifnull(sum(r.amazon_total_profit_cny), 0) / 100                     as '亚马逊总利润',
               ifnull(sum(r.tax_amount_cny), 0) / 100                              as '税金',
               ifnull(sum(truncate(r.sys_operating_expenses_cny, 0)), 0) / 100     as '运营费',
               -- ifnull(sum(truncate(r.sys_estimated_product_cost_cny, 0)), 0) /
               -- 100                                                                 as '预估产品成本',
               ifnull(sum(truncate(r.sys_product_cost_cny, 0)), 0) / 100           as '产品成本',
               -- ifnull(sum(r.sys_estimated_logistics_cny), 0) / 100                 as '预估头程',
               ifnull(sum(truncate(r.sys_logistics_cny, 0)), 0) / 100              as '头程费用',
               ifnull(sum(r.sys_gross_profit_cny), 0) / 100                        as '毛利润',
               ifnull(sum(truncate(r.sys_in_warehouse_cny, 0)), 0) / 100           as '在仓库存',
               ifnull(sum(truncate(r.sys_inventory_in_transit_cny, 0)), 0) / 100   as '在途库存',
               ifnull(sum(truncate(r.unshipped_inventory_cny, 0)), 0) / 100        as '未发货库存',
               ifnull(sum(truncate(r.sys_unpaid_headway_fee_cny, 0)), 0) /
               100                                                                 as '未支付头程费用',
               ifnull(sum(truncate(r.sys_total_deduction_cny, 0)), 0) / 100        as '75总扣费',
               ifnull(sum(truncate(r.sys_estimated_gross_profit_cny, 0)), 0) / 100 as '预估毛利润'
            from minerva_amazon_finance_event_sum r
                     join {{config.db_auth}}.minerva_employee e
                          on r.salesperson_id = e.id
                     left join minerva_smart_stocking_master mssm
                               on r.shop_master_id = mssm.amazon_shop_id
                                   and r.asin = mssm.asin
                                   and alive_flag in (0, 1)
                     left join minerva_amazon_shop_master masm
                               on masm.id = r.shop_master_id
            where calculation_dimension in (2, 3, 5, 7)
              and local_date between %s and %s
            group by r.salesperson_id, r.amazon_parent_asin, r.asin, r.shop_master_id
            order by 店铺;
    '''

    try:
        connection = pymysql.connect(
            host=config.DB_HOST,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            db=config.db_land
        )

        try:
            df = pd.read_sql(query, connection,params=[start_date, end_date])
        finally:
            connection.close()

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name=sheet_name)

        output.seek(0)
        workbook = load_workbook(output)
        final_output = apply_excel_styles(workbook)

        return send_file(
            final_output,
            download_name=file_name,
            as_attachment=True,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )

    except Exception as e:
        print(f"Error executing SQL: {e}")
        return jsonify({'error': 'Error executing SQL'}), 400

# 雅亭导出退货数据
@app.route('/export/return_data', methods=['GET'])
def return_data():
    start_date = request.args.get('startDate')
    end_date = request.args.get('endDate')

    sheet_name = "退货数据"
    file_name = f'{sheet_name}_{start_date}~{end_date}.xlsx'

    if not start_date or not end_date or start_date is None or end_date is None:
        return jsonify({'error': 'Time Error'}), 400
    if start_date > end_date:
        return jsonify({'error': 'Time Error'}), 400
    query = '''
        select
            r.return_date as `退回时间`,
            sm.shop_name  as `店铺`,
            r.asin as `asin`,
            m.salesperson_id as `运营`,
            m.product_name `成品名称`,
            m.is_new as `新品(1新品)`,
            m.product_category as `项目(1墙纸2地板贴3挂毯4其他5忙书)`,
            r.quantity as `数量`,
            r.reason as `原因`,
            r.customer_comments as `买家备注`
        from minerva_amazon_order_refund r
            left join minerva_amazon_order o 
                on r.amazon_order_id = o.amazon_order_id
            left join minerva_smart_stocking_master m 
                on r.account_id = m.account_id 
                and o.marketplace_id = m.shop_id
                and r.asin = m.asin
            left join minerva_amazon_shop_master sm
                on sm.shop_account_id = r.account_id
                and sm.marketplace_id = o.marketplace_id
        where r.return_date between %s and %s
    '''

    try:
        connection = pymysql.connect(
            host=config.DB_HOST,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            db=config.db_land
        )

        try:
            df = pd.read_sql(query, connection,params=[start_date, end_date])
        finally:
            connection.close()

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name=sheet_name)

        output.seek(0)
        workbook = load_workbook(output)
        final_output = apply_excel_styles(workbook)

        return send_file(
            final_output,
            download_name=file_name,
            as_attachment=True,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )

    except Exception as e:
        print(f"Error executing SQL: {e}")
        return jsonify({'error': 'Error executing SQL'}), 400

# 财务-导出店铺销售额
@app.route('/export/shop_sales/<int:year>/<int:month>', methods=['GET'])
def shop_sales(year,month):

    sheet_name = "店铺销售额"
    file_name = f'{sheet_name}_{year}-{month}.xlsx'

    if not year or not month or year is None or month is None:
        return jsonify({'error': 'Time Error'}), 400

    query = '''
        select
            s.shop_name as `店铺`,
            sum(r.order_quantity) as `订单量`,
            sum(r.product_quantity) as `销量`,
            sum(r.order_quantity_refund) as `退货单量`,
            sum(r.product_quantity_refund) as `退货销量`,
            truncate(sum(r.total_amount_usd) / 100, 2) as `销售额(美元)`,
            truncate(sum(r.total_amount_refund_usd) / 100, 2) as `退货销售额(美元)`
        from minerva_amazon_order_sku_report r
            join minerva_amazon_shop_master s on s.id = r.shop_master_id
        where year(r.sum_date) = %s
            and month(r.sum_date) = %s
            and r.shop_master_id != ''
        group by r.shop_master_id
        order by r.shop_master_id asc
    '''

    try:
        connection = pymysql.connect(
            host=config.DB_HOST,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            db=config.db_land
        )

        try:
            df = pd.read_sql(query, connection,params=[year, month])
        finally:
            connection.close()

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name=sheet_name)

        output.seek(0)
        workbook = load_workbook(output)
        final_output = apply_excel_styles(workbook)

        return send_file(
            final_output,
            download_name=file_name,
            as_attachment=True,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )

    except Exception as e:
        print(f"Error executing SQL: {e}")
        return jsonify({'error': 'Error executing SQL'}), 400

# 雅亭-按日期导出销量-销售额-上架时间
@app.route('/export/sale_data_daily', methods=['GET'])
def sale_data_daily():
    start_date = request.args.get('startDate')
    end_date = request.args.get('endDate')

    sheet_name = "每日销售数据"
    file_name = f'{sheet_name}_{start_date}~{end_date}.xlsx'

    if not start_date or not end_date or start_date is None or end_date is None:
        return jsonify({'error': 'Time Error'}), 400
    if start_date > end_date:
        return jsonify({'error': 'Time Error'}), 400

    query = '''
        select
            s.shop_name as `店铺`,
            r.asin as `asin`,
            min(me.name) as `运营`,
            min(mssm.product_name) as `成品名称`,
            min(mssm.fba_inventory_at) as `上架时间`,
            min(mssm.is_new) as '新品(1新品)',
            min(mfp.category) as '项目(1:墙纸 2:地板贴 3:挂毯 4:其他 5:忙书)',
            sum(r.order_quantity) as `订单量`,
            sum(r.product_quantity) as `销量`,
            sum(r.order_quantity_refund) as `退货单量`,
            sum(r.product_quantity_refund) as `退货销量`,
            truncate(sum(r.total_amount_usd) / 100, 2) as `销售额(美元)`,
            truncate(sum(r.total_amount_refund_usd) / 100, 2) as `退货销售额(美元)`
        from minerva_amazon_order_sku_report r
            join minerva_amazon_shop_master s on
                s.id = r.shop_master_id
            left join minerva_smart_stocking_master mssm 
            on r.shop_master_id = mssm.amazon_shop_id
                and r.asin = mssm.asin
            left join minerva_finished_product mfp on mssm.product_id = mfp.id
            left join {{config.db_auth}}.minerva_employee me on me.id = mssm.salesperson_id
        where r.sum_date between %s and %s
            and r.shop_master_id != ''
        group by r.shop_master_id,r.asin
        order by r.shop_master_id asc
    '''

    try:
        connection = pymysql.connect(
            host=config.DB_HOST,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            db=config.db_land
        )

        try:
            df = pd.read_sql(query, connection,params=[start_date, end_date])
        finally:
            connection.close()

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name=sheet_name)

        output.seek(0)
        workbook = load_workbook(output)
        final_output = apply_excel_styles(workbook)

        return send_file(
            final_output,
            download_name=file_name,
            as_attachment=True,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )

    except Exception as e:
        print(f"Error executing SQL: {e}")
        return jsonify({'error': 'Error executing SQL'}), 400

# 雅亭-每月导出销量-销售额-上架时间
@app.route('/export/sale_data_monthly', methods=['GET'])
def sale_data_monthly():
    start_date = request.args.get('startDate')
    end_date = request.args.get('endDate')

    sheet_name = "每月销售数据"
    file_name = f'{sheet_name}_{start_date}~{end_date}.xlsx'

    if not start_date or not end_date or start_date is None or end_date is None:
        return jsonify({'error': 'Time Error'}), 400
    if start_date > end_date:
        return jsonify({'error': 'Time Error'}), 400

    query = '''
        select
            year(r.sum_date) as `年`, 
            month(r.sum_date) as `月`,
            s.shop_name as `店铺`,
            r.asin as `asin`,
            min(mssm.product_name) as `成品名称`,
            min(mssm.fba_inventory_at) as `上架时间`,
            sum(r.order_quantity) as `订单量`,
            sum(r.product_quantity) as `销量`,
            sum(r.order_quantity_refund) as `退货单量`,
            sum(r.product_quantity_refund) as `退货销量`,
            truncate(sum(r.total_amount_usd) / 100, 2) as `销售额(美元)`,
            truncate(sum(r.total_amount_refund_usd) / 100, 2) as `退货销售额(美元)`
        from minerva_amazon_order_sku_report r
            join minerva_amazon_shop_master s on s.id = r.shop_master_id
            left join minerva_smart_stocking_master mssm 
                on r.shop_master_id = mssm.amazon_shop_id
                and r.asin = mssm.asin
        where r.sum_date between %s and %s
        and r.shop_master_id != ''
        group by r.shop_master_id, r.asin, year(r.sum_date), month(r.sum_date)
        order by `年`, `月`, s.shop_name asc;
    '''

    try:
        connection = pymysql.connect(
            host=config.DB_HOST,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            db=config.db_land
        )

        try:
            df = pd.read_sql(query, connection, params=[start_date, end_date])
        finally:
            connection.close()

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name=sheet_name)

        output.seek(0)
        workbook = load_workbook(output)
        final_output = apply_excel_styles(workbook)

        return send_file(
            final_output,
            download_name=file_name,
            as_attachment=True,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )

    except Exception as e:
        print(f"Error executing SQL: {e}")
        return jsonify({'error': 'Error executing SQL'}), 400

# 日/月查询店铺的广告活动的数据代码
@app.route('/export/shop_advertise_data', methods=['GET'])
def shop_advertise_data():
    date_granularity = request.args.get('dateGranularity')
    start_date = request.args.get('startDate')
    end_date = request.args.get('endDate')
    account_id = request.args.get('accountId')
    marketplace_id = request.args.get('marketplaceId')

    sheet_name = "广告活动"
    file_name = f'{sheet_name}_{start_date}~{end_date}.xlsx'

    if not start_date or not end_date:
        return jsonify({'error': 'Time Error'}), 400
    if start_date is None or end_date is None:
        return jsonify({'error': 'Time Error'}), 400
    if start_date > end_date:
        return jsonify({'error': 'Time Error'}), 400
    if account_id is None or marketplace_id is None:
        return jsonify({'error': 'Account or marketplace error'}), 400
    if date_granularity is None or date_granularity not in ['day','month']:
        return jsonify({'error': 'Date Granularity Error'}), 400

    query = '''
        select *
        from minerva_amazon_ads_advertised_purchased_product_granularity
            where date_granularity = %s
            and start_time >= %s
            and end_time <= %s
            and account_id = %s
            and marketplace_id = %s
    '''

    try:
        connection = pymysql.connect(
            host=config.DB_HOST,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            db=config.db_land
        )

        try:
            df = pd.read_sql(query, connection, params=[date_granularity,start_date, end_date,account_id,marketplace_id])
        finally:
            connection.close()

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name=sheet_name)

        output.seek(0)
        workbook = load_workbook(output)
        final_output = apply_excel_styles(workbook)

        return send_file(
            final_output,
            download_name=file_name,
            as_attachment=True,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )

    except Exception as e:
        print(f"Error executing SQL: {e}")
        return jsonify({'error': 'Error executing SQL'}), 400

# 导出指定时间段财务付款拒绝原因
@app.route('/export/refuse_pay_reason', methods=['GET'])
def refuse_pay_reason():
    start_date = request.args.get('startDate')
    end_date = request.args.get('endDate')

    sheet_name = "财务拒绝付款原因"
    file_name = f'{sheet_name}_{start_date}~{end_date}.xlsx'

    if not start_date or not end_date or start_date is None or end_date is None:
        return jsonify({'error': 'Time Error'}), 400
    if start_date > end_date:
        return jsonify({'error': 'Time Error'}), 400

    query = '''
        select payment.coding     as 付款编号,
               patment_emp.name   as 发起人,
               approve_emp.name   as 节点审批人,
               approve.opinion    as 拒绝原因,
               payment.created_at as 发起时间
        from minerva_finance_payment_record as payment
                 join {{config.db_auth}}.minerva_employee as patment_emp on payment.created_by = patment_emp.id
                 join minerva_finance_payment_approve as approve on payment.id = approve.master_id
                 left join {{config.db_auth}}.minerva_employee as approve_emp on approve.approver_id = approve_emp.id
        where payment.created_at between %s and %s
            and approve.status in (6, 7);
    '''

    try:
        connection = pymysql.connect(
            host=config.DB_HOST,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            db=config.db_land
        )

        try:
            df = pd.read_sql(query, connection, params=[start_date, end_date])
        finally:
            connection.close()

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name=sheet_name)

        output.seek(0)
        workbook = load_workbook(output)
        final_output = apply_excel_styles(workbook)

        return send_file(
            final_output,
            download_name=file_name,
            as_attachment=True,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )

    except Exception as e:
        print(f"Error executing SQL: {e}")
        return jsonify({'error': 'Error executing SQL'}), 400

# 基于库存分类账与接受的月度产品成本
@app.route('/export/monthly_product_cost_in_inventoryLedger', methods=['GET'])
def monthly_product_cost_in_inventoryLedger():
    start_date = request.args.get('startDate')
    end_date = request.args.get('endDate')

    sheet_name = "月度产品成本"
    file_name = f'{sheet_name}_{start_date}~{end_date}.xlsx'

    if not start_date or not end_date or start_date is None or end_date is None:
        return jsonify({'error': 'Time Error'}), 400
    if start_date > end_date:
        return jsonify({'error': 'Time Error'}), 400

    query = '''
        select payment.coding     as 付款编号,
               patment_emp.name   as 发起人,
               approve_emp.name   as 节点审批人,
               approve.opinion    as 拒绝原因,
               payment.created_at as 发起时间
        from minerva_finance_payment_record as payment
                 join {{config.db_auth}}.minerva_employee as patment_emp on payment.created_by = patment_emp.id
                 join minerva_finance_payment_approve as approve on payment.id = approve.master_id
                 left join {{config.db_auth}}.minerva_employee as approve_emp on approve.approver_id = approve_emp.id
        where payment.created_at between %s and %s
            and approve.status in (6, 7);
    '''

    try:
        connection = pymysql.connect(
            host=config.DB_HOST,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            db=config.db_land
        )

        try:
            df = pd.read_sql(query, connection, params=[start_date, end_date])
        finally:
            connection.close()

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name=sheet_name)

        output.seek(0)
        workbook = load_workbook(output)
        final_output = apply_excel_styles(workbook)

        return send_file(
            final_output,
            download_name=file_name,
            as_attachment=True,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )

    except Exception as e:
        print(f"Error executing SQL: {e}")
        return jsonify({'error': 'Error executing SQL'}), 400

if __name__ == '__main__':
    app.run(debug=True)
